﻿using System;
using System.Data;

namespace YArchitech.LIB
{
	public class SQLiteDataBaseHelper
	{
		private SQLiteDataBaseHelper()
		{
		}

		public SQLiteDataBaseHelper(string filePath)
		{
			this.filePath = filePath;
			SQLiteDataOperate.SetDataSource(filePath);
		}

		public DataTable GetTableSchema(string tableName)
		{
			return SQLiteDataOperate.ExecQuery("select * from " + tableName);
		}

		public DataTable GetAllName(string tableName)
		{
			return SQLiteDataOperate.ExecQuery("select id,[Name],Family,Image,Image2D from " + tableName);
		}

		public DataTable GetAllNameAndImage2DByCategory(string tableName, int type)
		{
			string sqlStr;
			if (type == 0)
			{
				sqlStr = "select id,[Name],Image2D,Image from " + tableName;
			}
			else
			{
				sqlStr = string.Concat(new object[]
				{
					"select id,[Name],Image2D,Image from ",
					tableName,
					" where Category=",
					type
				});
			}
			return SQLiteDataOperate.ExecQuery(sqlStr);
		}

		public DataTable GetAllNameAndCategory(string tableName)
		{
			return SQLiteDataOperate.ExecQuery("select id,[Name],Family,Image,Image2D,Category from " + tableName);
		}

		public DataTable GetInfoById(string tableName, int id)
		{
			return SQLiteDataOperate.ExecQuery(string.Concat(new object[]
			{
				"select * from ",
				tableName,
				" where id=",
				id
			}));
		}

		public DataTable GetInfoByName(string tableName, string name)
		{
			return SQLiteDataOperate.ExecQuery(string.Concat(new string[]
			{
				"select * from ",
				tableName,
				" where [Name]='",
				name,
				"'"
			}));
		}

		public DataTable GetTableInfoByName(string tableName, string name)
		{
			string sqlStr;
			if (name == "全部水箱" || name == "")
			{
				sqlStr = "select * from " + tableName;
			}
			else
			{
				sqlStr = string.Concat(new string[]
				{
					"select * from ",
					tableName,
					" where [Id] in (select Id from WaterTank  where [Name]='",
					name,
					"')"
				});
			}
			return SQLiteDataOperate.ExecQuery(sqlStr);
		}

		public bool SetInfoById(string tableName, int id, string sqlcondition)
		{
			return SQLiteDataOperate.ExecuteCommand(string.Concat(new object[]
			{
				"UPDATE ",
				tableName,
				" SET ",
				sqlcondition,
				" WHERE Id=",
				id
			}));
		}

		public DataTable GetCategoryInfoByName(string name)
		{
			return SQLiteDataOperate.ExecQuery("select a.Id,a.Name,a.CategoryId,a.Image2D,a.Image,a.Family from CategoryInfo a right join Category b where a.Name='" + name + "' and a.id=b.ParentId");
		}

		public DataTable GetAllNameAndParentId(string tableName)
		{
			return SQLiteDataOperate.ExecQuery("select id,[Name],Family,ParentId from " + tableName);
		}

		public DataTable GetInfoByParentId(string tableName, int parentId)
		{
			return SQLiteDataOperate.ExecQuery(string.Concat(new object[]
			{
				"select * from ",
				tableName,
				" where ParentId=",
				parentId
			}));
		}

		public DataTable GetDBTable(string sql)
		{
			return SQLiteDataOperate.ExecQuery(sql);
		}

		private string filePath = "";
	}
}
